USE CelestialDB
GO
/*****************************************************************************************/
/* Validate Login */
CREATE PROCEDURE uspValidateEmployeeLogin(
	@Username VARCHAR(50),
	@Password BINARY(16)
)
AS
BEGIN
SELECT * FROM tblEmployees WHERE Username = @Username AND [Password]=@Password
END
GO
/*****************************************************************************************/
/* Insert Employee */
CREATE PROCEDURE uspInsertEmployee
	@Username			VARCHAR(15),
	@Password			BINARY(16),
	@UserLevel			INT,
	--EmployeeID IDENTITY
	@EmployedOn			DATE,
	
	@FirstName			VARCHAR(30),
	@LastName			VARCHAR(30),
	@Contact			VARCHAR(15),
	@Email				VARCHAR(50),
	@DOB				DATE,
	
	@AddressLine1		VARCHAR(30),
	@AddressLine2		VARCHAR(30),
	@PostalCode			VARCHAR(10),
	
	@IsActive			BIT
AS
BEGIN
	INSERT INTO tblEmployees 
	VALUES(@Username, @Password, @UserLevel, @EmployedOn, @FirstName, @LastName, @Contact, @Email, @DOB,
		   @AddressLine1, @AddressLine2, @PostalCode, @IsActive)
END
GO
/*****************************************************************************************/
/* Update Employee */
CREATE PROCEDURE uspUpdateEmployee
	--Username PRIMARY
	@Password			BINARY(16),
	@UserLevel			INT,
	@EmployeeID			INT,
	@EmployedOn			DATE,
	
	@FirstName			VARCHAR(30),
	@LastName			VARCHAR(30),
	@Contact			VARCHAR(15),
	@Email				VARCHAR(50),
	@DOB				DATE,
	
	@AddressLine1		VARCHAR(30),
	@AddressLine2		VARCHAR(30),
	@PostalCode			VARCHAR(10),
	
	@IsActive			BIT
AS
BEGIN
	UPDATE tblEmployees 
	SET [Password] = @Password,
		UserLevel = @UserLevel,
		EmployedOn = @EmployedOn,
		
		FirstName = @FirstName,
		LastName = @LastName,	
		Contact = @Contact,				
		Email = @Email,	
		DOB = @DOB,					
		AddressLine1 = @AddressLine1,		
		AddressLine2 = @AddressLine2,		
		PostalCode = @PostalCode,			

		IsActive = @IsActive	
	
	WHERE EmployeeID = @EmployeeID
END

GO
/*****************************************************************************************/
/* Update Employee */
CREATE PROCEDURE uspDeleteEmployee
	@EmployeeID INT
AS
BEGIN
	DELETE FROM tblEmployees WHERE EmployeeID=@EmployeeID
END

GO
/*****************************************************************************************/
/* GetEmployee */
CREATE PROCEDURE uspGetEmployee
	@EmployeeID INT
AS
BEGIN
	SELECT * FROM tblEmployees WHERE EmployeeID = @EmployeeID
END
GO
/*****************************************************************************************/
/* GetNextEmployeeID */
CREATE PROCEDURE uspGetNextEmployeeID
AS
BEGIN
	SELECT IDENT_CURRENT('tblEmployees') + IDENT_INCR('tblEmployees')
END
/*****************************************************************************************/
